﻿#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 修改人：
 * 时间：
 * 修改说明：
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using seejee_Backendium.Core.Interfaces;
using seejee_Backendium.Core.Models;

namespace seejee_Backendium.Data.Services
{
    public class DynamicConnectionService : IDynamicConnectionService
    {
        private readonly IConfiguration _configuration;

        public DynamicConnectionService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        #region Windows 身份验证连接方法

        private string BuildWindowsConnectionString(string serverIp, string databaseName)
        {
            return $"Server={serverIp};Database={databaseName};Trusted_Connection=true;TrustServerCertificate=true;MultipleActiveResultSets=true;Connect Timeout=30;";
        }

        public async Task<bool> TestConnectionAsync(string serverIp, string databaseName)
        {
            var connectionString = BuildWindowsConnectionString(serverIp, databaseName);
            return await TestConnectionAsync(connectionString);
        }

        public async Task<List<string>> GetDatabasesAsync(string serverIp)
        {
            var databases = new List<string>();
            var connectionString = BuildWindowsConnectionString(serverIp, "master");

            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SqlCommand(
                        "SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0", // 排除系统数据库和离线数据库
                        connection);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            databases.Add(reader["name"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException($"获取数据库列表失败: {ex.Message}", ex);
            }

            return databases;
        }

        public async Task<string> ExecuteQueryAsync(string serverIp, string databaseName, string query)
        {
            var connectionString = BuildWindowsConnectionString(serverIp, databaseName);

            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new SqlCommand(query, connection))
                    {
                        var result = await command.ExecuteScalarAsync();
                        return result?.ToString() ?? "No results";
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException($"执行查询失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region SQL Server 身份验证连接方法

        // 构建连接字符串
        public string BuildConnectionString(BIDBContentDto dbInfo)
        {
            if (dbInfo == null)
                throw new ArgumentNullException(nameof(dbInfo));

            var builder = new SqlConnectionStringBuilder
            {
                DataSource = dbInfo.cIP,
                InitialCatalog = dbInfo.cDBName,
                UserID = dbInfo.cDBUser,
                Password = dbInfo.cDBPassword,
                TrustServerCertificate = true,
                MultipleActiveResultSets = true,
                ConnectTimeout = 30
            };

            return builder.ToString();
        }

        // 测试数据库连接（使用连接字符串）
        public async Task<bool> TestConnectionAsync(string connectionString)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    await connection.CloseAsync();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        // 测试数据库连接（使用 BIDBContentDto）
        public async Task<bool> TestConnectionAsync(BIDBContentDto dbInfo)
        {
            if (dbInfo == null)
                throw new ArgumentNullException(nameof(dbInfo));

            string connectionString = BuildConnectionString(dbInfo);
            return await TestConnectionAsync(connectionString);
        }

        #endregion

        #region 通用数据库操作方法

        // 使用自定义连接字符串获取数据库列表
        public async Task<List<string>> GetDatabasesWithCustomConnectionAsync(string connectionString)
        {
            var databases = new List<string>();

            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SqlCommand(
                        "SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0",
                        connection);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            databases.Add(reader["name"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException($"获取数据库列表失败: {ex.Message}", ex);
            }

            return databases;
        }

        // 使用自定义连接字符串执行查询
        public async Task<string> ExecuteQueryWithCustomConnectionAsync(string connectionString, string query)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new SqlCommand(query, connection))
                    {
                        var result = await command.ExecuteScalarAsync();
                        return result?.ToString() ?? "No results";
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException($"执行查询失败: {ex.Message}", ex);
            }
        }

        #endregion
    }
}